"""
教师管理API视图
@file admin/teacher_admin.py
@description 处理教师管理相关的API请求
@created 2024-06-28
"""
from django.http import JsonResponse
from django.views.decorators.http import require_http_methods
from django.views.decorators.csrf import csrf_exempt
import json
import logging
from django.db import connection
from django.utils.dateparse import parse_datetime
from datetime import datetime
from admin.views import require_role # 添加此行

# 设置日志
logger = logging.getLogger(__name__)

# ================================================
# YZH表前缀API - 操作yzh_colleges, yzh_departments, yzh_teachers表
# ================================================

def dictfetchall(cursor):
    """将游标获取的结果转换为字典列表"""
    columns = [col[0] for col in cursor.description]
    return [dict(zip(columns, row)) for row in cursor.fetchall()]

@require_http_methods(["GET"])
def yzh_college_list(request):
    """
    获取学院列表（使用yzh_colleges表）
    @description 获取所有学院的信息
    @param request: HTTP请求
    @return: JsonResponse 学院列表
    """
    try:
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT id, name, description, status, create_time
                FROM yzh_colleges
                ORDER BY id
            """)
            colleges = dictfetchall(cursor)
            
        return JsonResponse({
            'status': 'success',
            'message': '获取学院列表成功',
            'data': {
                'colleges': colleges
            }
        })
    except Exception as e:
        logger.exception(f"获取学院列表失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'message': f'获取学院列表失败: {str(e)}'
        }, status=500)

@require_http_methods(["GET"])
def yzh_department_list(request, college_id):
    """
    获取学院下的系部列表（使用yzh_departments表）
    @description 获取指定学院下的所有系部信息
    @param request: HTTP请求
    @param college_id: 学院ID
    @return: JsonResponse 系部列表
    """
    try:
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT id, name, college_id, description, status, create_time
                FROM yzh_departments
                WHERE college_id = %s
                ORDER BY id
            """, [college_id])
            departments = dictfetchall(cursor)
            
        return JsonResponse({
            'status': 'success',
            'message': '获取系部列表成功',
            'data': {
                'departments': departments
            }
        })
    except Exception as e:
        logger.exception(f"获取系部列表失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'message': f'获取系部列表失败: {str(e)}'
        }, status=500)

@require_http_methods(["GET"])
def yzh_teacher_list(request):
    """
    获取教师列表（使用yzh_teachers表）
    @description 获取教师列表，支持分页和搜索
    @param request: HTTP请求
    @return: JsonResponse 教师列表
    """
    try:
        # 获取查询参数
        limit = request.GET.get('limit', 1000)
        offset = request.GET.get('offset', 0)
        search = request.GET.get('search', '')
        
        with connection.cursor() as cursor:
            # 基本查询
            query = """
                SELECT t.id, t.teacher_code, t.name, t.gender, t.title, 
                       t.college_id, c.name as college_name,
                       t.department_id, d.name as department_name,
                       t.email, t.phone, t.status, t.create_time
                FROM yzh_teachers t
                LEFT JOIN yzh_colleges c ON t.college_id = c.id
                LEFT JOIN yzh_departments d ON t.department_id = d.id
            """
            
            # 添加搜索条件
            params = []
            if search:
                query += " WHERE t.name LIKE %s OR t.teacher_code LIKE %s"
                params.extend([f'%{search}%', f'%{search}%'])
                
            # 添加排序和分页
            query += " ORDER BY t.id LIMIT %s OFFSET %s"
            params.extend([int(limit), int(offset)])
            
            # 执行查询
            cursor.execute(query, params)
            teachers = dictfetchall(cursor)
            
        return JsonResponse({
            'status': 'success',
            'message': '获取教师列表成功',
            'data': {
                'teachers': teachers,
                'total': len(teachers)
            }
        })
    except Exception as e:
        logger.exception(f"获取教师列表失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'message': f'获取教师列表失败: {str(e)}'
        }, status=500)

@require_http_methods(["GET"])
def yzh_teacher_detail(request, teacher_id):
    """
    获取教师详情（使用yzh_teachers表）
    @description 获取指定教师的详细信息
    @param request: HTTP请求
    @param teacher_id: 教师ID
    @return: JsonResponse 教师详情
    """
    try:
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT t.id, t.teacher_code, t.name, t.gender, t.title, 
                       t.college_id, c.name as college_name,
                       t.department_id, d.name as department_name,
                       t.email, t.phone, t.status, t.create_time
                FROM yzh_teachers t
                LEFT JOIN yzh_colleges c ON t.college_id = c.id
                LEFT JOIN yzh_departments d ON t.department_id = d.id
                WHERE t.id = %s
            """, [teacher_id])
            result = dictfetchall(cursor)
            
            if not result:
                return JsonResponse({
                    'status': 'error',
                    'message': '教师不存在'
                }, status=404)
                
            teacher = result[0]
            
        return JsonResponse({
            'status': 'success',
            'message': '获取教师详情成功',
            'data': {
                'teacher': teacher
            }
        })
    except Exception as e:
        logger.exception(f"获取教师详情失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'message': f'获取教师详情失败: {str(e)}'
        }, status=500)

@csrf_exempt
@require_http_methods(["POST"])
def yzh_add_teacher(request):
    """
    添加教师（使用yzh_teachers表）
    @description 添加新教师信息
    @param request: HTTP请求
    @return: JsonResponse 添加结果
    """
    try:
        # 解析请求数据
        data = json.loads(request.body)
        
        # 打印日志
        logger.info(f"添加教师: {data}")
        
        # 验证必填字段
        required_fields = ['name', 'teacher_code', 'college_id', 'department_id']
        for field in required_fields:
            if field not in data or not data[field]:
                return JsonResponse({
                    'status': 'error',
                    'message': f'缺少必填字段: {field}'
                }, status=400)
        
        # 检查工号是否已存在
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT COUNT(*) FROM yzh_teachers WHERE teacher_code = %s
            """, [data['teacher_code']])
            count = cursor.fetchone()[0]
            if count > 0:
                return JsonResponse({
                    'status': 'error',
                    'message': '教师工号已存在'
                }, status=400)
            
            # 创建教师记录
            cursor.execute("""
                INSERT INTO yzh_teachers (teacher_code, name, gender, title, college_id, department_id, email, phone, status)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
                RETURNING id
            """, [
                data['teacher_code'],
                data['name'],
                data.get('gender', ''),
                data.get('title', ''),
                data['college_id'],
                data['department_id'],
                data.get('email', ''),
                data.get('phone', ''),
                1  # 默认状态为1（正常）
            ])
            new_id = cursor.fetchone()[0]
        
        # 返回成功结果
        return JsonResponse({
            'status': 'success',
            'message': '教师添加成功',
            'data': {
                'teacher_id': new_id
            }
        })
        
    except json.JSONDecodeError:
        logger.error("请求体JSON解析错误")
        return JsonResponse({
            'status': 'error',
            'message': '请求体格式错误，应为JSON格式'
        }, status=400)
    except Exception as e:
        logger.exception(f"添加教师失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'message': f'添加教师失败: {str(e)}'
        }, status=500)

@csrf_exempt
@require_http_methods(["PUT"])
def yzh_update_teacher(request, teacher_id):
    """
    更新教师信息（使用yzh_teachers表）
    @description: 更新指定教师的信息
    @param request: HTTP请求
    @param teacher_id: 教师ID
    @return: JsonResponse 更新结果
    """
    try:
        # 解析请求数据
        data = json.loads(request.body)
        
        # 打印日志
        logger.info(f"更新教师 {teacher_id}: {data}")
        
        # 检查教师是否存在
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT COUNT(*) FROM yzh_teachers WHERE id = %s
            """, [teacher_id])
            count = cursor.fetchone()[0]
            if count == 0:
                return JsonResponse({
                    'status': 'error',
                    'message': '教师不存在'
                }, status=404)
            
            # 如果更新了教师工号，检查是否与其他教师冲突
            if 'teacher_code' in data:
                cursor.execute("""
                    SELECT COUNT(*) FROM yzh_teachers 
                    WHERE teacher_code = %s AND id != %s
                """, [data['teacher_code'], teacher_id])
                count = cursor.fetchone()[0]
                if count > 0:
                    return JsonResponse({
                        'status': 'error',
                        'message': '教师工号已被其他教师使用'
                    }, status=400)
            
            # 构建更新SQL
            update_fields = []
            params = []
            
            # 检查并添加可更新的字段
            field_mapping = {
                'name': 'name',
                'teacher_code': 'teacher_code',
                'gender': 'gender',
                'title': 'title',
                'college_id': 'college_id',
                'department_id': 'department_id',
                'email': 'email',
                'phone': 'phone'
            }
            
            for key, field_name in field_mapping.items():
                if key in data:
                    update_fields.append(f"{field_name} = %s")
                    params.append(data[key])
            
            if not update_fields:
                return JsonResponse({
                    'status': 'error',
                    'message': '没有提供要更新的字段'
                }, status=400)
                
            # 添加教师ID到参数列表
            params.append(teacher_id)
            
            # 执行更新
            query = f"""
                UPDATE yzh_teachers
                SET {', '.join(update_fields)}
                WHERE id = %s
            """
            cursor.execute(query, params)
        
        # 返回成功结果
        return JsonResponse({
            'status': 'success',
            'message': '教师信息更新成功'
        })
        
    except json.JSONDecodeError:
        logger.error("请求体JSON解析错误")
        return JsonResponse({
            'status': 'error',
            'message': '请求体格式错误，应为JSON格式'
        }, status=400)
    except Exception as e:
        logger.exception(f"更新教师失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'message': f'更新教师失败: {str(e)}'
        }, status=500)

@csrf_exempt
@require_http_methods(["DELETE"])
def yzh_delete_teacher(request, teacher_id):
    """
    删除教师（使用yzh_teachers表）
    @description: 删除指定教师
    @param request: HTTP请求
    @param teacher_id: 教师ID
    @return: JsonResponse 删除结果
    """
    try:
        logger.info(f"删除教师: {teacher_id}")
        
        # 检查教师是否存在
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT COUNT(*) FROM yzh_teachers WHERE id = %s
            """, [teacher_id])
            count = cursor.fetchone()[0]
            if count == 0:
                return JsonResponse({
                    'status': 'error',
                    'message': '教师不存在'
                }, status=404)
            
            # 删除教师记录
            cursor.execute("""
                DELETE FROM yzh_teachers WHERE id = %s
            """, [teacher_id])
        
        # 返回成功结果
        return JsonResponse({
            'status': 'success',
            'message': '教师删除成功'
        })
        
    except Exception as e:
        logger.exception(f"删除教师失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'message': f'删除教师失败: {str(e)}'
        }, status=500)

# ================================================
# 原有API函数
# ================================================

@csrf_exempt
@require_http_methods(["POST"])
def add_teacher(request):
    """
    添加教师
    @description 添加新教师信息
    @param request: HTTP请求
    @return: JsonResponse 添加结果
    """
    try:
        # 解析请求数据
        data = json.loads(request.body)
        
        # 打印日志
        logger.info(f"添加教师: {data}")
        
        # 验证必填字段
        required_fields = ['teacher_name', 'teacher_code']
        for field in required_fields:
            if field not in data or not data[field]:
                return JsonResponse({
                    'status': 'error',
                    'message': f'缺少必填字段: {field}'
                }, status=400)
        
        # 检查工号是否已存在
        from admin.models import Teacher
        if Teacher.objects.filter(teacher_code=data['teacher_code']).exists():
            return JsonResponse({
                'status': 'error',
                'message': '教师工号已存在'
            }, status=400)
        
        # 创建教师记录
        teacher = Teacher.objects.create(
            teacher_name=data['teacher_name'],
            teacher_code=data['teacher_code'],
            title=data.get('title', ''),
            college_id=data.get('college_id'),
            department=data.get('department', ''),
            education=data.get('education', ''),
            specialty=data.get('specialty', ''),
            entry_date=data.get('entry_date')
        )
        
        # 返回成功结果
        return JsonResponse({
            'status': 'success',
            'message': '教师添加成功',
            'data': {
                'teacher_id': teacher.teacher_id
            }
        })
        
    except json.JSONDecodeError:
        logger.error("请求体JSON解析错误")
        return JsonResponse({
            'status': 'error',
            'message': '请求体格式错误，应为JSON格式'
        }, status=400)
    except Exception as e:
        logger.exception(f"添加教师失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'message': f'添加教师失败: {str(e)}'
        }, status=500)

@csrf_exempt
@require_http_methods(["PUT"])
def update_teacher(request, teacher_id):
    """
    更新教师信息
    @description: 更新指定教师的信息
    @param request: HTTP请求
    @param teacher_id: 教师ID
    @return: JsonResponse 更新结果
    """
    try:
        # 解析请求数据
        data = json.loads(request.body)
        
        # 打印日志
        logger.info(f"更新教师 {teacher_id}: {data}")
        
        # 查找教师记录
        from admin.models import Teacher
        try:
            teacher = Teacher.objects.get(teacher_id=teacher_id)
        except Teacher.DoesNotExist:
            return JsonResponse({
                'status': 'error',
                'message': '教师不存在'
            }, status=404)
        
        # 如果更新了教师工号，检查是否与其他教师冲突
        if 'teacher_code' in data and data['teacher_code'] != teacher.teacher_code:
            if Teacher.objects.filter(teacher_code=data['teacher_code']).exclude(teacher_id=teacher_id).exists():
                return JsonResponse({
                    'status': 'error',
                    'message': '教师工号已被其他教师使用'
                }, status=400)
        
        # 更新教师信息
        if 'teacher_name' in data:
            teacher.teacher_name = data['teacher_name']
        if 'teacher_code' in data:
            teacher.teacher_code = data['teacher_code']
        if 'title' in data:
            teacher.title = data['title']
        if 'college_id' in data:
            teacher.college_id = data['college_id']
        if 'department' in data:
            teacher.department = data['department']
        if 'education' in data:
            teacher.education = data['education']
        if 'specialty' in data:
            teacher.specialty = data['specialty']
        if 'entry_date' in data:
            teacher.entry_date = data['entry_date']
        
        # 保存更新
        teacher.save()
        
        # 返回成功结果
        return JsonResponse({
            'status': 'success',
            'message': '教师信息更新成功'
        })
        
    except json.JSONDecodeError:
        logger.error("请求体JSON解析错误")
        return JsonResponse({
            'status': 'error',
            'message': '请求体格式错误，应为JSON格式'
        }, status=400)
    except Exception as e:
        logger.exception(f"更新教师失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'message': f'更新教师失败: {str(e)}'
        }, status=500)

@csrf_exempt
@require_http_methods(["DELETE"])
def delete_teacher(request, teacher_id):
    """
    删除教师
    @description: 删除指定教师
    @param request: HTTP请求
    @param teacher_id: 教师ID
    @return: JsonResponse 删除结果
    """
    try:
        logger.info(f"删除教师: {teacher_id}")
        
        # 查找教师记录
        from admin.models import Teacher
        try:
            teacher = Teacher.objects.get(teacher_id=teacher_id)
        except Teacher.DoesNotExist:
            return JsonResponse({
                'status': 'error',
                'message': '教师不存在'
            }, status=404)
        
        # TODO: 检查教师是否有关联数据，如授课记录等，根据业务需要决定是否允许删除
        
        # 删除教师
        teacher.delete()
        
        # 返回成功结果
        return JsonResponse({
            'status': 'success',
            'message': '教师删除成功'
        })
        
    except Exception as e:
        logger.exception(f"删除教师失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'message': f'删除教师失败: {str(e)}'
        }, status=500)

@csrf_exempt
@require_http_methods(["POST"])
def batch_delete_teachers(request):
    """
    批量删除教师
    @description: 批量删除多名教师
    @param request: HTTP请求，包含教师ID列表
    @return: JsonResponse 删除结果
    """
    try:
        # 解析请求数据
        data = json.loads(request.body)
        teacher_ids = data.get('teacher_ids', [])
        
        if not teacher_ids:
            return JsonResponse({
                'status': 'error',
                'message': '未提供教师ID'
            }, status=400)
        
        logger.info(f"批量删除教师: {teacher_ids}")
        
        # 查找并删除教师
        from admin.models import Teacher
        deleted_count = 0
        for teacher_id in teacher_ids:
            try:
                teacher = Teacher.objects.get(teacher_id=teacher_id)
                teacher.delete()
                deleted_count += 1
            except Teacher.DoesNotExist:
                # 记录不存在的教师ID但继续执行
                logger.warning(f"教师ID {teacher_id} 不存在")
        
        # 返回成功结果
        return JsonResponse({
            'status': 'success',
            'message': f'成功删除 {deleted_count} 名教师',
            'data': {
                'deleted_count': deleted_count
            }
        })
        
    except json.JSONDecodeError:
        logger.error("请求体JSON解析错误")
        return JsonResponse({
            'status': 'error',
            'message': '请求体格式错误，应为JSON格式'
        }, status=400)
    except Exception as e:
        logger.exception(f"批量删除教师失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'message': f'批量删除教师失败: {str(e)}'
        }, status=500)

@csrf_exempt
@require_http_methods(["POST"])
def import_teachers(request):
    """
    导入教师
    @description: 从Excel文件导入教师信息
    @param request: HTTP请求，包含教师信息Excel文件
    @return: JsonResponse 导入结果
    """
    try:
        # 检查是否有文件上传
        if 'file' not in request.FILES:
            return JsonResponse({
                'status': 'error',
                'message': '未上传文件'
            }, status=400)
        
        file = request.FILES['file']
        logger.info(f"导入教师文件: {file.name}")
        
        # TODO: 实现Excel文件解析和数据导入逻辑
        # 这里只是一个示例，实际需要根据Excel文件的格式和业务需求实现导入逻辑
        
        # 返回临时成功结果
        return JsonResponse({
            'status': 'success',
            'message': '教师导入功能尚未实现',
            'data': {
                'imported_count': 0
            }
        })
        
    except Exception as e:
        logger.exception(f"导入教师失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'message': f'导入教师失败: {str(e)}'
        }, status=500)

@csrf_exempt
@require_http_methods(["GET"])
def export_teachers(request):
    """
    导出教师
    @description: 将教师信息导出为Excel文件
    @param request: HTTP请求
    @return: Excel文件响应或错误信息
    """
    try:
        logger.info("导出教师信息")
        
        # TODO: 实现教师信息导出为Excel文件的逻辑
        # 这里只是一个示例，实际需要根据业务需求实现导出逻辑
        
        # 返回临时成功结果（实际应该返回文件）
        return JsonResponse({
            'status': 'success',
            'message': '教师导出功能尚未实现'
        })
        
    except Exception as e:
        logger.exception(f"导出教师失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'message': f'导出教师失败: {str(e)}'
        }, status=500) 
# 新增视图函数
def yzh_college_detail_view(request, college_id):
    """
    获取单个学院详情 (yzh_colleges)
    """
    if request.method == 'GET':
        try:
            with connection.cursor() as cursor:
                cursor.execute("SELECT id, name, description, status, create_time FROM yzh_colleges WHERE id = %s", [college_id])
                college = cursor.fetchone()
                if college:
                    # 将 create_time 转换为 ISO 格式字符串
                    create_time_iso = college[4].isoformat() if college[4] else None
                    college_data = {
                        'id': college[0],
                        'name': college[1],
                        'description': college[2],
                        'status': college[3],
                        'create_time': create_time_iso
                    }
                    return JsonResponse({'status': 'success', 'data': college_data})
                else:
                    return JsonResponse({'status': 'error', 'message': 'College not found'}, status=404)
        except Exception as e:
            return JsonResponse({'status': 'error', 'message': str(e)}, status=500)
    return JsonResponse({'status': 'error', 'message': 'Invalid request method'}, status=405)

def yzh_college_classes_view(request, college_id):
    """
    获取指定学院下的所有班级列表 (yzh_classes)
    关联查询 yzh_departments 获取系部名称
    """
    if request.method == 'GET':
        try:
            with connection.cursor() as cursor:
                # 查询班级信息，并关联 yzh_departments 获取系部名称
                # 注意：这里假设 yzh_classes 有 department_id 字段来关联 yzh_departments
                # 并且 yzh_classes 有 college_id 字段
                sql = """
                    SELECT 
                        yc.id, yc.class_code, yc.name, yc.grade_year, 
                        yc.department_id, yd.name as department_name, 
                        yc.college_id, yc.status, yc.create_time,
                        (SELECT COUNT(*) FROM yzh_student_class ysc WHERE ysc.class_id = yc.id) as student_count
                    FROM yzh_classes yc
                    LEFT JOIN yzh_departments yd ON yc.department_id = yd.id
                    WHERE yc.college_id = %s
                """
                cursor.execute(sql, [college_id])
                classes_raw = cursor.fetchall()
                
                classes_list = []
                for cls in classes_raw:
                    create_time_iso = cls[8].isoformat() if cls[8] else None
                    classes_list.append({
                        'id': cls[0],
                        'class_code': cls[1],
                        'name': cls[2],
                        'grade_year': cls[3], # 前端期望 grade_id 和 grade_name
                        'grade_id': cls[3], # 暂时用 grade_year 作为 grade_id
                        'grade_name': str(cls[3]) + '级', # 构造 grade_name
                        'department_id': cls[4],
                        'department_name': cls[5], # 系部名称
                        'college_id': cls[6],
                        'status': cls[7],
                        'create_time': create_time_iso,
                        'student_count': cls[9], # 学生数量
                        'teacher_name': 'N/A' # 教师名称目前无法直接从此查询获得，设为N/A
                    })
            return JsonResponse({'status': 'success', 'data': {'classes': classes_list}})
        except Exception as e:
            return JsonResponse({'status': 'error', 'message': str(e)}, status=500)
    return JsonResponse({'status': 'error', 'message': 'Invalid request method'}, status=405)

# Placeholder for yzh_teacher_courses
@require_http_methods(["GET"])
@require_role(['school_admin', 'college_admin', 'class_admin']) # 修改此处的装饰器
def yzh_teacher_courses(request, teacher_id):
    """
    获取指定教师的课程和班级列表
    API: GET /api/admin/yzh_teachers/<teacher_id>/courses/
    """
    try:
        # 尝试将 teacher_id 转换为整数，如果已经是字符串ID，则保持原样或根据实际主键类型处理
        # 假设 yzh_teachers.id 是字符类型如 'T0001'，则不需要转换或进行相应处理
        # 如果 yzh_teachers.id 是整数，则 int(teacher_id) 是正确的
        # 从您的 teacher_id='T0001' 来看，它可能是字符串。
        # 假设 yzh_teaching_assignments.teacher_id 也是这个类型。

        # 数据库表:
        # yzh_teaching_assignments: id, teacher_id, course_id, class_id, semester, year, status, create_time
        # yzh_courses: id, course_code, name, credit, hours, department_id, college_id, description, status, create_time
        # yzh_classes: id, class_code, name, grade_year, department_id, college_id, status, create_time

        with connection.cursor() as cursor:
            sql = """
                    SELECT 
                    yc.name AS course_name,
                    ycs.name AS class_name,
                    ta.id AS assignment_id,
                    yc.id AS course_id,      -- 使用 yzh_courses.id
                    ycs.id AS class_id,      -- 使用 yzh_classes.id
                    yc.course_code,
                    ycs.grade_year          -- 使用 yzh_classes.grade_year (假设为学年)
                FROM
                    yzh_teaching_assignments ta
                JOIN
                    yzh_courses yc ON ta.course_id = yc.id -- 确保关联 yzh_courses.id
                JOIN
                    yzh_classes ycs ON ta.class_id = ycs.id -- 确保关联 yzh_classes.id
                WHERE
                    ta.teacher_id = %s
            """
            cursor.execute(sql, [teacher_id])
            # 假设 dictfetchall 是在同模块或已正确导入的函数
            # 如果 dictfetchall 不在当前文件，确保它已从 admin.views 或其他地方导入
            # from admin.views import dictfetchall # 可能需要这样的导入
            results = dictfetchall(cursor) 
                
        if results:
            # results已经是字典列表了，可以直接返回
            return JsonResponse({'status': 'success', 'message': '获取教师课程成功', 'data': results})
        else:
            return JsonResponse({'status': 'success', 'message': '该教师暂无授课安排', 'data': []})

    except ValueError as ve: # 特别处理 teacher_id 转换错误（如果需要转换）
        logger.error(f"获取教师课程失败 - 无效的 teacher_id 格式 (teacher_id: {teacher_id}): {ve}")
        return JsonResponse({'status': 'error', 'message': f'无效的教师ID格式: {ve}'}, status=400)
    except Exception as e:
        logger.error(f"获取教师课程失败 (teacher_id: {teacher_id}): {e}")
        return JsonResponse({'status': 'error', 'message': f'获取教师课程及班级列表失败: {str(e)}'}, status=500)

# Placeholder for yzh_teacher_course_classes
@require_http_methods(["GET"])
def yzh_teacher_course_classes(request, teacher_id, course_id):
    """
    获取指定教师在指定课程下教授的班级列表
    @description 根据教师ID和课程ID从 yzh_teaching_assignments 和 yzh_classes 表获取班级信息
    @param request: HTTP请求
    @param teacher_id: 教师ID
    @param course_id: 课程ID
    @return: JsonResponse 班级列表
    """
    try:
        with connection.cursor() as cursor:
            # 查询教师在指定课程下教授的班级信息
            # 假设 yzh_teaching_assignments 关联了 teacher_id, course_id, class_id
            # 假设 yzh_classes 存储了班级详情，包括 student_count
            sql = """
                SELECT 
                    ycs.id, 
                    ycs.name, 
                    ycs.class_code,
                    ycs.grade_year,
                    ycs.department_id,
                    yd.name as department_name,
                    ycs.college_id,
                    yco.name as college_name,
                    ycs.student_count, -- 确保 yzh_classes 表有 student_count 字段
                    ycs.status,
                    ycs.create_time,
                    yta.semester,
                    yta.year
                FROM yzh_classes ycs
                JOIN yzh_teaching_assignments yta ON ycs.id = yta.class_id
                LEFT JOIN yzh_departments yd ON ycs.department_id = yd.id
                LEFT JOIN yzh_colleges yco ON ycs.college_id = yco.id
                WHERE yta.teacher_id = %s AND yta.course_id = %s
                ORDER BY ycs.id;
            """
            cursor.execute(sql, [teacher_id, course_id])
            classes_raw = dictfetchall(cursor)
            
            classes_list = []
            for cls in classes_raw:
                create_time_iso = cls['create_time'].isoformat() if cls.get('create_time') else None
                classes_list.append({
                    'id': cls['id'],
                    'name': cls['name'],
                    'class_code': cls.get('class_code'),
                    'grade_year': cls.get('grade_year'),
                    'department_id': cls.get('department_id'),
                    'department_name': cls.get('department_name'),
                    'college_id': cls.get('college_id'),
                    'college_name': cls.get('college_name'),
                    'student_count': cls.get('student_count', 0), # 如果没有提供，默认为0
                    'status': cls.get('status'),
                    'create_time': create_time_iso,
                    'semester': cls.get('semester'),
                    'year': cls.get('year')
                })

        return JsonResponse({
            'status': 'success',
            'message': f'获取教师 {teacher_id} 在课程 {course_id} 下的班级列表成功',
            'data': {
                'classes': classes_list
            }
        })
    except Exception as e:
        logger.exception(f"获取教师 {teacher_id} 在课程 {course_id} 下的班级列表失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'message': f'获取教师课程班级列表失败: {str(e)}'
        }, status=500) 

@require_http_methods(["GET"])
# @require_role(['school_admin', 'college_admin', 'class_admin'])
def yzh_class_student_scores(request, teacher_id, course_id, class_id):
    try:
        teacher_id_int = int(teacher_id)
        course_id_int = int(course_id)
        class_id_int = int(class_id)

        students_data = []
        # analysis_results 用于存储计算出的统计值
        analysis_results = {
            'total_students': 0,
            'average_score': 0.0,
            'pass_rate': 0.0,
            'excellent_rate': 0.0, # 确保计算此值
            'score_distribution': {
                '90-100': 0, '80-89': 0, '70-79': 0, '60-69': 0, '0-59': 0,
            }
        }
        teaching_assignment_id = None
        class_name_val, course_name_val, teacher_name_val, semester_val, year_val = '', '', '', '', ''

        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT id, semester, year FROM yzh_teaching_assignments
                WHERE teacher_id = %s AND course_id = %s AND class_id = %s
            """, [teacher_id_int, course_id_int, class_id_int])
            assignment_raw = cursor.fetchone()
            
            if not assignment_raw:
                # ... (处理未找到教学安排的情况，可以只返回学生列表，analysisData 为初始值或特定消息)
                # 为了简洁，如果严格要求教学安排，则直接返回错误或空数据
                cursor.execute("""
                    SELECT s.id as student_id, s.student_code, s.name
                    FROM yzh_students s
                    WHERE s.class_id = %s AND s.status = 1 ORDER BY s.student_code;
                """, [class_id_int])
                raw_students_only = dictfetchall(cursor)
                for row_s in raw_students_only:
                    students_data.append({
                        'student_id': row_s['student_id'],
                        'student_code': row_s['student_code'],
                        'name': row_s['name'], # API返回 name
                        'score': '未关联教学', 'grade_id': None,
                    })
                analysis_results['total_students'] = len(students_data)
                # 获取班级、课程、教师名称 (即使没有教学安排，也尝试获取)
                cursor.execute("SELECT name FROM yzh_classes WHERE id = %s", [class_id_int])
                cn = cursor.fetchone()
                class_name_val = cn[0] if cn else f'班级ID {class_id_int}'
                cursor.execute("SELECT name FROM yzh_courses WHERE id = %s", [course_id_int])
                con = cursor.fetchone()
                course_name_val = con[0] if con else f'课程ID {course_id_int}'
                cursor.execute("SELECT name FROM yzh_teachers WHERE id = %s", [teacher_id_int])
                tn = cursor.fetchone()
                teacher_name_val = tn[0] if tn else f'教师ID {teacher_id_int}'
                
                return JsonResponse({ # Corrected indentation for this block
        'status': 'info',
                    'message': '未找到有效教学安排，仅显示学生列表。',
        'data': {
                        'students': students_data, 'analysis': analysis_results,
                        'class_id': class_id_int, 'course_id': course_id_int, 'teacher_id': teacher_id_int,
                        'class_name': class_name_val, 'course_name': course_name_val, 
                        'teacher_name': teacher_name_val, 'semester': '', 'year': ''
                    }
                })

            teaching_assignment_id = assignment_raw[0]
            semester_val = assignment_raw[1]
            year_val = str(assignment_raw[2]) if assignment_raw[2] else ''


            # 获取班级、课程、教师名称
            cursor.execute("SELECT name FROM yzh_classes WHERE id = %s", [class_id_int])
            cn = cursor.fetchone()
            class_name_val = cn[0] if cn else f'班级ID {class_id_int}'
            cursor.execute("SELECT name FROM yzh_courses WHERE id = %s", [course_id_int])
            con = cursor.fetchone()
            course_name_val = con[0] if con else f'课程ID {course_id_int}'
            cursor.execute("SELECT name FROM yzh_teachers WHERE id = %s", [teacher_id_int])
            tn = cursor.fetchone()
            teacher_name_val = tn[0] if tn else f'教师ID {teacher_id_int}'


            sql_students_grades = """
                SELECT s.id as student_id, s.student_code, s.name, g.score as final_score, g.id as grade_id
                FROM yzh_students s
                LEFT JOIN yzh_grades g ON s.id = g.student_id AND g.teaching_assignment_id = %s
                WHERE s.class_id = %s AND s.status = 1 ORDER BY s.student_code;
            """
            cursor.execute(sql_students_grades, [teaching_assignment_id, class_id_int])
            raw_students_grades = dictfetchall(cursor)

            all_scores_values = []
            # analysis_results['total_students'] is already set from student_count_in_class
            # student_count_in_class = len(raw_students_grades)
            # analysis_results['total_students'] = student_count_in_class

            for row in raw_students_grades:
                students_data.append({
                    'student_id': row['student_id'], 
                    'student_code': row['student_code'], 
                    'name': row['name'], # API返回 name
                    'score': row['final_score'] if row['final_score'] is not None else '未评分',
                    'grade_id': row['grade_id'],
                    # 确保其他前端可能期望的字段（如 daily_score, exam_score）在此处填充
                    # 如果它们不由 yzh_grades 提供，则可能需要其他表或默认为 '未评分' 或 null
                    'daily_score': None, # 示例：假设目前没有这些数据
                    'exam_score': None   # 示例：假设目前没有这些数据
                })
                if row['final_score'] is not None:
                    try:
                        all_scores_values.append(float(row['final_score']))
                    except (ValueError, TypeError):
                        logger.warning(f"无法转换成绩 {row['final_score']} 为浮点数，学生ID: {row['student_id']}")

            # 重新计算统计数据，确保所有字段都被填充
            if all_scores_values:
                analysis_results['average_score'] = round(sum(all_scores_values) / len(all_scores_values), 1)
                
                passed_count = sum(1 for s_val in all_scores_values if s_val >= 60)
                excellent_count = sum(1 for s_val in all_scores_values if s_val >= 90) # 优秀：>=90分
                
                analysis_results['pass_rate'] = round((passed_count / len(all_scores_values)) * 100, 1) if len(all_scores_values) > 0 else 0.0
                analysis_results['excellent_rate'] = round((excellent_count / len(all_scores_values)) * 100, 1) if len(all_scores_values) > 0 else 0.0
                
                analysis_results['score_distribution']['90-100'] = sum(1 for s_val in all_scores_values if 90 <= s_val <= 100)
                analysis_results['score_distribution']['80-89'] = sum(1 for s_val in all_scores_values if 80 <= s_val < 90)
                analysis_results['score_distribution']['70-79'] = sum(1 for s_val in all_scores_values if 70 <= s_val < 80)
                analysis_results['score_distribution']['60-69'] = sum(1 for s_val in all_scores_values if 60 <= s_val < 70)
                analysis_results['score_distribution']['0-59'] = sum(1 for s_val in all_scores_values if s_val < 60)
            else: # 如果没有有效成绩，则将相关统计数据设为0或初始状态
                analysis_results['average_score'] = 0.0
                analysis_results['pass_rate'] = 0.0
                analysis_results['excellent_rate'] = 0.0
                for key in analysis_results['score_distribution']:
                    analysis_results['score_distribution'][key] = 0
            
            # total_students 应该基于班级花名册人数（raw_students_grades 的长度）
            analysis_results['total_students'] = len(raw_students_grades)


        # 主成功路径的返回语句
        return JsonResponse({
            'status': 'success',
            'message': '获取学生成绩及分析成功',
            'data': {
                'students': students_data,
                'analysis': analysis_results, # 包含所有统计信息的对象
                
                # 页眉信息直接在 data 对象下
                'class_name': class_name_val,
                'course_name': course_name_val,
                'teacher_name': teacher_name_val,
                'semester': semester_val,
                'year': year_val,
                
                # 其他相关ID
                'class_id': class_id_int,
                'course_id': course_id_int,
                'teacher_id': teacher_id_int,
                'teaching_assignment_id': teaching_assignment_id
            }
        })
    except ValueError:
        logger.error(f"无效的ID格式 teacher_id:{teacher_id}, course_id:{course_id}, class_id:{class_id}")
        return JsonResponse({'status': 'error', 'message': '提供的ID格式无效'}, status=400)
    except Exception as e:
        logger.exception(f"获取班级学生成绩及分析失败: {e}")
        return JsonResponse({'status': 'error', 'message': f'服务器错误: {str(e)}'}, status=500)

@require_http_methods(["GET"])
def yzh_class_detail_view(request, class_id):
    """
    获取单个班级详情 (yzh_classes)
    关联 yzh_departments 获取系部名称
    班主任信息暂时移除，待确定关联方式
    """
    try:
        with connection.cursor() as cursor:
            sql = """
                SELECT 
                    yc.id, yc.class_code, yc.name, yc.grade_year, 
                    yc.department_id, yd.name as department_name, 
                    yc.college_id, yc.status, yc.create_time
                FROM yzh_classes yc
                LEFT JOIN yzh_departments yd ON yc.department_id = yd.id
                WHERE yc.id = %s
            """
            cursor.execute(sql, [class_id])
            cls = cursor.fetchone()
            
            if cls:
                create_time_iso = cls[8].isoformat() if cls[8] else None
                class_data = {
                    'id': cls[0],
                    'class_code': cls[1],
                    'name': cls[2],
                    'grade_year': cls[3],
                    'grade_name': str(cls[3]) + '级',
                    'department_id': cls[4],
                    'department_name': cls[5],
                    'college_id': cls[6],
                    'status': cls[7],
                    'create_time': create_time_iso,
                    'teacher_id': None, # 暂时设置为 None
                    'teacher_name': 'N/A' # 暂时设置为 N/A
            }
                return JsonResponse({'status': 'success', 'data': class_data})
            else:
                return JsonResponse({'status': 'error', 'message': 'Class not found'}, status=404)
    except Exception as e:
        logger.error(f"Error fetching class detail for ID {class_id}: {str(e)}")
        return JsonResponse({'status': 'error', 'message': str(e)}, status=500)

@require_http_methods(["GET"])
def yzh_class_students_view(request, class_id):
    """
    获取班级下的学生列表（使用yzh_students表和yzh_classes表）
    @description 获取指定班级下的所有学生信息
    @param request: HTTP请求
    @param class_id: 班级ID
    @return: JsonResponse 学生列表
    """
    try:
        logger.info(f"开始获取班级学生列表，班级ID: {class_id}")
        
        # 确保class_id是整数
        try:
            class_id = int(class_id)
            logger.info(f"班级ID转换为整数: {class_id}")
        except (ValueError, TypeError):
            logger.error(f"班级ID格式错误，无法转换为整数: {class_id}")
            return JsonResponse({
                'status': 'error',
                'message': f'班级ID格式错误: {class_id}'
            }, status=400)
        
        with connection.cursor() as cursor:
            # 先检查班级是否存在
            logger.info("执行查询检查班级是否存在")
            try:
                cursor.execute("""
                    SELECT id, name, grade_year
                    FROM yzh_classes
                    WHERE id = %s
                """, [class_id])
                class_result_raw = cursor.fetchone() # 使用 fetchone 获取单行
            except Exception as e:
                logger.error(f"查询班级信息失败: {str(e)}")
                return JsonResponse({
                    'status': 'error',
                    'message': f'查询班级信息失败: {str(e)}'
                }, status=500)

            if not class_result_raw:
                logger.warning(f'班级 (ID: {class_id}) 不存在')
                return JsonResponse({
                    'status': 'error',
                    'message': f'班级 (ID: {class_id}) 不存在'
                }, status=404)
            
            # 获取列名，并将结果转换为字典
            class_columns = [col[0] for col in cursor.description]
            class_info = dict(zip(class_columns, class_result_raw))
            
            # 添加grade_name字段
            if 'grade_year' in class_info:
                class_info['grade_name'] = f"{class_info['grade_year']}级"
            
            logger.info(f"班级信息获取成功: {class_info}")
            
            # 获取班级下的学生列表
            logger.info("执行查询获取班级学生列表")
            try:
                cursor.execute("""
                    SELECT s.id, s.student_code, s.name, s.gender, 
                           s.enrollment_date, s.status
                    FROM yzh_students s
                    WHERE s.class_id = %s AND s.status = 1
                    ORDER BY s.student_code
                """, [class_id])
                students = dictfetchall(cursor)
                logger.info(f"班级 {class_id} 学生列表查询成功，返回 {len(students)} 条记录")
            except Exception as e:
                logger.error(f"查询班级学生列表失败: {str(e)}")
                return JsonResponse({
                    'status': 'error',
                    'message': f'查询班级学生列表失败: {str(e)}'
                }, status=500)
        
        # 确保返回格式符合前端期望
        response_data = {
            'status': 'success',
            'message': '获取班级学生列表成功',
            'data': {
                'class_info': class_info,
                'students': students
            }
        }
        
        logger.info("成功构建班级学生列表响应")
        return JsonResponse(response_data)
        
    except Exception as e:
        logger.exception(f"获取班级学生列表失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'message': f'获取班级学生列表失败: {str(e)}'
        }, status=500)

@require_http_methods(["GET"])
def yzh_class_list(request):
    """
    获取班级列表（使用yzh_classes表）
    @description 获取班级列表，支持分页和搜索
    @param request: HTTP请求
    @return: JsonResponse 班级列表
    """
    try:
        # 获取查询参数
        limit = request.GET.get('limit', 1000)
        offset = request.GET.get('offset', 0)
        search = request.GET.get('search', '')
        college_id = request.GET.get('college_id', None)
        grade_year = request.GET.get('grade_year', None)
        
        with connection.cursor() as cursor:
            # 构建查询SQL
            query = """
                SELECT c.id, c.name, c.grade_year, CONCAT(c.grade_year, '级') as grade_name, 
                       c.college_id, co.name as college_name,
                       c.department_id, d.name as department_name,
                       c.teacher_id, t.name as teacher_name,
                       c.student_count, c.status, c.create_time
                FROM yzh_classes c
                LEFT JOIN yzh_colleges co ON c.college_id = co.id
                LEFT JOIN yzh_departments d ON c.department_id = d.id
                LEFT JOIN yzh_teachers t ON c.teacher_id = t.id
                WHERE 1=1
            """
            
            # 准备参数
            params = []
            
            # 添加搜索条件
            if search:
                query += " AND c.name LIKE %s"
                params.append(f'%{search}%')
            
            # 按学院筛选
            if college_id:
                query += " AND c.college_id = %s"
                params.append(college_id)
                
            # 按年级筛选
            if grade_year:
                query += " AND c.grade_year = %s"
                params.append(grade_year)
            
            # 添加排序和分页
            query += " ORDER BY c.grade_year DESC, c.id LIMIT %s OFFSET %s"
            params.extend([int(limit), int(offset)])
            
            # 执行查询
            cursor.execute(query, params)
            classes = dictfetchall(cursor)
            
            # 获取总数量
            count_query = """
                SELECT COUNT(*) as total
                FROM yzh_classes c
                WHERE 1=1
            """
            count_params = []
            
            if search:
                count_query += " AND c.name LIKE %s"
                count_params.append(f'%{search}%')
                
            if college_id:
                count_query += " AND c.college_id = %s"
                count_params.append(college_id)
                
            if grade_year:
                count_query += " AND c.grade_year = %s"
                count_params.append(grade_year)
                
            cursor.execute(count_query, count_params)
            total_count = cursor.fetchone()[0]
            
        return JsonResponse({
            'status': 'success',
            'message': '获取班级列表成功',
            'data': {
                'classes': classes,
                'total': total_count
            }
        })
    except Exception as e:
        logger.exception(f"获取班级列表失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'message': f'获取班级列表失败: {str(e)}'
        }, status=500)

@csrf_exempt
@require_http_methods(["POST"])
def yzh_add_class(request):
    """
    添加班级（使用yzh_classes表）
    @description 添加新班级信息
    @param request: HTTP请求
    @return: JsonResponse 添加结果
    """
    try:
        # 解析请求数据
        data = json.loads(request.body)
        name = data.get('name')
        grade_year = data.get('grade_year')
        grade_name = data.get('grade_name')
        college_id = data.get('college_id')
        department_id = data.get('department_id')
        teacher_id = data.get('teacher_id', None)
        
        # 验证必填项
        if not name or not grade_year or not grade_name or not college_id:
            return JsonResponse({
                'status': 'error',
                'message': '名称、年级ID、年级名称和学院ID为必填项'
            }, status=400)
        
        with connection.cursor() as cursor:
            # 检查是否存在同名班级
            cursor.execute("SELECT id FROM yzh_classes WHERE name = %s", [name])
            if cursor.fetchone():
                return JsonResponse({
                    'status': 'error',
                    'message': f'班级 {name} 已存在'
                }, status=400)
            
            # 插入班级记录
            cursor.execute("""
                INSERT INTO yzh_classes (name, grade_year, grade_name, college_id, 
                                        department_id, teacher_id, student_count, status)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
            """, [
                name, grade_year, grade_name, college_id, 
                department_id, teacher_id, 0, 1
            ])
            
            # 获取新插入班级的ID
            cursor.execute("SELECT LAST_INSERT_ID()")
            class_id = cursor.fetchone()[0]
            
            # 获取新插入的班级详情
            cursor.execute("""
                SELECT c.id, c.name, c.grade_year, c.grade_name, 
                       c.college_id, co.name as college_name,
                       c.department_id, d.name as department_name,
                       c.teacher_id, t.name as teacher_name,
                       c.student_count, c.status, c.create_time
                FROM yzh_classes c
                LEFT JOIN yzh_colleges co ON c.college_id = co.id
                LEFT JOIN yzh_departments d ON c.department_id = d.id
                LEFT JOIN yzh_teachers t ON c.teacher_id = t.id
                WHERE c.id = %s
            """, [class_id])
            new_class = dictfetchall(cursor)[0]
            
        return JsonResponse({
            'status': 'success',
            'message': '添加班级成功',
            'data': {
                'class': new_class
            }
        })
    except Exception as e:
        logger.exception(f"添加班级失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'message': f'添加班级失败: {str(e)}'
        }, status=500)

@csrf_exempt
@require_http_methods(["PUT"])
def yzh_update_class(request, class_id):
    """
    更新班级信息（使用yzh_classes表）
    @description 更新指定班级的信息
    @param request: HTTP请求
    @param class_id: 班级ID
    @return: JsonResponse 更新结果
    """
    try:
        # 解析请求数据
        data = json.loads(request.body)
        name = data.get('name')
        grade_year = data.get('grade_year')
        grade_name = data.get('grade_name')
        college_id = data.get('college_id')
        department_id = data.get('department_id')
        teacher_id = data.get('teacher_id')
        status = data.get('status')
        
        # 验证必填项
        if not name or not grade_year or not grade_name or not college_id:
            return JsonResponse({
                'status': 'error',
                'message': '名称、年级ID、年级名称和学院ID为必填项'
            }, status=400)
            
        with connection.cursor() as cursor:
            # 检查班级是否存在
            cursor.execute("SELECT id FROM yzh_classes WHERE id = %s", [class_id])
            if not cursor.fetchone():
                return JsonResponse({
                    'status': 'error',
                    'message': f'班级 (ID: {class_id}) 不存在'
                }, status=404)
            
            # 检查是否存在其他同名班级
            cursor.execute("SELECT id FROM yzh_classes WHERE name = %s AND id != %s", [name, class_id])
            if cursor.fetchone():
                return JsonResponse({
                    'status': 'error',
                    'message': f'已存在同名班级 {name}'
                }, status=400)
            
            # 更新班级记录
            set_clause = "name = %s, grade_year = %s, grade_name = %s, college_id = %s, department_id = %s"
            params = [name, grade_year, grade_name, college_id, department_id]
            
            if teacher_id is not None:
                set_clause += ", teacher_id = %s"
                params.append(teacher_id)
                
            if status is not None:
                set_clause += ", status = %s"
                params.append(status)
                
            # 添加WHERE条件参数
            params.append(class_id)
            
            cursor.execute(f"UPDATE yzh_classes SET {set_clause} WHERE id = %s", params)
            
            # 获取更新后的班级详情
            cursor.execute("""
                SELECT c.id, c.name, c.grade_year, c.grade_name, 
                       c.college_id, co.name as college_name,
                       c.department_id, d.name as department_name,
                       c.teacher_id, t.name as teacher_name,
                       c.student_count, c.status, c.create_time
                FROM yzh_classes c
                LEFT JOIN yzh_colleges co ON c.college_id = co.id
                LEFT JOIN yzh_departments d ON c.department_id = d.id
                LEFT JOIN yzh_teachers t ON c.teacher_id = t.id
                WHERE c.id = %s
            """, [class_id])
            updated_class = dictfetchall(cursor)[0]
            
        return JsonResponse({
            'status': 'success',
            'message': '更新班级成功',
            'data': {
                'class': updated_class
            }
        })
    except Exception as e:
        logger.exception(f"更新班级失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'message': f'更新班级失败: {str(e)}'
        }, status=500)

@csrf_exempt
@require_http_methods(["DELETE"])
def yzh_delete_class(request, class_id):
    """
    删除班级（使用yzh_classes表）
    @description 删除指定班级的信息
    @param request: HTTP请求
    @param class_id: 班级ID
    @return: JsonResponse 删除结果
    """
    try:
        with connection.cursor() as cursor:
            # 检查班级是否存在
            cursor.execute("SELECT id, name, student_count FROM yzh_classes WHERE id = %s", [class_id])
            result = cursor.fetchone()
            if not result:
                return JsonResponse({
                    'status': 'error',
                    'message': f'班级 (ID: {class_id}) 不存在'
                }, status=404)
                
            class_name = result[1]
            student_count = result[2] or 0
            
            # 检查班级是否有学生
            if student_count > 0:
                return JsonResponse({
                    'status': 'error',
                    'message': f'班级 {class_name} 下有 {student_count} 名学生，无法直接删除'
                }, status=400)
            
            # 检查班级是否有相关的教学安排
            cursor.execute("SELECT COUNT(*) FROM yzh_teaching_assignments WHERE class_id = %s", [class_id])
            teaching_count = cursor.fetchone()[0]
            if teaching_count > 0:
                return JsonResponse({
                    'status': 'error',
                    'message': f'班级 {class_name} 有 {teaching_count} 个相关的教学安排，无法直接删除'
                }, status=400)
            
            # 直接删除班级
            cursor.execute("DELETE FROM yzh_classes WHERE id = %s", [class_id])
            
        return JsonResponse({
            'status': 'success',
            'message': f'成功删除班级 {class_name}'
        })
    except Exception as e:
        logger.exception(f"删除班级失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'message': f'删除班级失败: {str(e)}'
        }, status=500)

# 学生管理相关API函数

@csrf_exempt
@require_http_methods(["POST"])
def yzh_add_student(request):
    """
    添加学生（使用yzh_students表）
    @description 添加新学生信息
    @param request: HTTP请求
    @return: JsonResponse 添加结果
    """
    try:
        # 解析请求数据
        data = json.loads(request.body)
        
        logger.info(f"添加学生请求数据: {data}")
        
        # 获取必要字段
        student_code = data.get('student_code')
        name = data.get('name')
        gender = data.get('gender', '')
        class_id = data.get('class_id')
        enrollment_date = data.get('enrollment_date')
        
        # 验证必填项
        if not student_code or not name or not class_id:
            return JsonResponse({
                'status': 'error',
                'message': '学号、姓名和班级ID为必填项'
            }, status=400)
            
        # 处理日期 - 使用简单字符串处理
        date_str = enrollment_date if enrollment_date else datetime.now().strftime('%Y-%m-%d')
        
        student_id = None
        department_id = None
        college_id = None
        try:
            # 使用原始SQL执行所有操作
            with connection.cursor() as cursor:
                # 检查班级是否存在
                cursor.execute("SELECT id FROM yzh_classes WHERE id = %s", [class_id])
                class_result = cursor.fetchone()
                if not class_result:
                    return JsonResponse({
                        'status': 'error',
                        'message': f'班级 (ID: {class_id}) 不存在'
                    }, status=404)
                
                # 检查学号是否已存在
                cursor.execute("SELECT id FROM yzh_students WHERE student_code = %s", [student_code])
                if cursor.fetchone():
                    return JsonResponse({
                        'status': 'error',
                        'message': f'学号 {student_code} 已存在'
                    }, status=400)
                
                # 从班级获取系部和学院ID
                cursor.execute("SELECT department_id, college_id FROM yzh_classes WHERE id = %s", [class_id])
                class_info = cursor.fetchone()
                if class_info:
                    department_id = data.get('department_id') or class_info[0]
                    college_id = data.get('college_id') or class_info[1]
                    
                # 插入学生记录 - 使用最基本的字段
                insert_sql = """
                    INSERT INTO yzh_students 
                    (student_code, name, gender, class_id, department_id, college_id, enrollment_date, status) 
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                """
                cursor.execute(insert_sql, [
                    student_code, 
                    name, 
                    gender, 
                    class_id,
                    department_id,
                    college_id,
                    date_str,
                    1  # 默认状态为1（正常）
                ])
                
                # 获取新ID
                cursor.execute("SELECT LAST_INSERT_ID()")
                result = cursor.fetchone()
                student_id = result[0] if result else None
                
                # 更新班级的学生数量
                cursor.execute("UPDATE yzh_classes SET student_count = student_count + 1 WHERE id = %s", [class_id])
        except Exception as e:
            logger.exception(f"数据库操作失败: {str(e)}")
            return JsonResponse({
                'status': 'error',
                'message': f'数据库操作失败: {str(e)}'
            }, status=500)
            
        # 不从数据库获取数据，直接构建简单响应
        student_data = {
            'id': student_id,
            'student_code': student_code,
            'name': name,
            'gender': gender,
            'class_id': class_id
        }
        
        # 返回极简响应
        return JsonResponse({
            'status': 'success',
            'message': '添加学生成功',
            'data': {'student': student_data}
        }, safe=True, json_dumps_params={'ensure_ascii': False})
    except json.JSONDecodeError:
        return JsonResponse({'status': 'error', 'message': 'JSON解析错误'}, status=400)
    except Exception as e:
        logger.exception(f"添加学生失败: {str(e)}")
        return JsonResponse({'status': 'error', 'message': '服务器内部错误'}, status=500)

@csrf_exempt
@require_http_methods(["PUT"])
def yzh_update_student(request, student_id):
    """
    更新学生信息（使用yzh_students表）
    @description 更新指定学生的信息
    @param request: HTTP请求
    @param student_id: 学生ID
    @return: JsonResponse 更新结果
    """
    try:
        # 解析请求数据
        data = json.loads(request.body)
        
        logger.info(f"更新学生ID={student_id}请求数据: {data}")
        
        # 获取主要字段
        student_code = data.get('student_code')
        name = data.get('name')
        gender = data.get('gender', '')
        class_id = data.get('class_id')
        enrollment_date = data.get('enrollment_date')
        
        # 验证必填项
        if not student_code or not name:
            return JsonResponse({
                'status': 'error',
                'message': '学号和姓名为必填项'
            }, status=400)
        
        old_class_id = None
        try:
            with connection.cursor() as cursor:
                # 验证学生是否存在
                cursor.execute("SELECT class_id FROM yzh_students WHERE id = %s", [student_id])
                result = cursor.fetchone()
                if not result:
                    return JsonResponse({
                        'status': 'error',
                        'message': f'学生 (ID: {student_id}) 不存在'
                    }, status=404)
                
                old_class_id = result[0]
                
                # 检查学号是否已存在（排除当前学生）
                cursor.execute("SELECT id FROM yzh_students WHERE student_code = %s AND id != %s", 
                              [student_code, student_id])
                if cursor.fetchone():
                    return JsonResponse({
                        'status': 'error',
                        'message': f'学号 {student_code} 已被其他学生使用'
                    }, status=400)
                
                # 构建更新字段和参数
                update_fields = []
                params = []
                
                if student_code:
                    update_fields.append("student_code = %s")
                    params.append(student_code)
                    
                if name:
                    update_fields.append("name = %s")
                    params.append(name)
                
                update_fields.append("gender = %s")
                params.append(gender)
                
                if enrollment_date:
                    update_fields.append("enrollment_date = %s")
                    params.append(enrollment_date)
                
                if class_id:
                    update_fields.append("class_id = %s")
                    params.append(class_id)
                
                # 执行更新
                if update_fields:
                    params.append(student_id)  # 添加WHERE条件的参数
                    update_sql = f"UPDATE yzh_students SET {', '.join(update_fields)} WHERE id = %s"
                    cursor.execute(update_sql, params)
                    
                    # 如果更改了班级，更新班级学生数量
                    if class_id and old_class_id and int(class_id) != int(old_class_id):
                        # 增加新班级学生数
                        cursor.execute("UPDATE yzh_classes SET student_count = student_count + 1 WHERE id = %s", 
                                      [class_id])
                        # 减少旧班级学生数
                        cursor.execute("UPDATE yzh_classes SET student_count = student_count - 1 WHERE id = %s", 
                                      [old_class_id])
        except Exception as e:
            logger.exception(f"数据库操作失败: {str(e)}")
            return JsonResponse({
                'status': 'error',
                'message': f'数据库操作失败: {str(e)}'
            }, status=500)
        
        # 构建简化的返回数据
        student_data = {
            'id': student_id,
            'student_code': student_code,
            'name': name,
            'gender': gender,
            'class_id': class_id or old_class_id
        }
        
        # 返回极简响应
        return JsonResponse({
            'status': 'success',
            'message': '更新学生成功',
            'data': {'student': student_data}
        }, safe=True, json_dumps_params={'ensure_ascii': False})
    except json.JSONDecodeError:
        return JsonResponse({'status': 'error', 'message': 'JSON解析错误'}, status=400)
    except Exception as e:
        logger.exception(f"更新学生失败: {str(e)}")
        return JsonResponse({'status': 'error', 'message': '服务器内部错误'}, status=500)

@csrf_exempt
@require_http_methods(["DELETE"])
def yzh_delete_student(request, student_id):
    """
    删除学生（使用yzh_students表）
    @description 删除指定学生的信息
    @param request: HTTP请求
    @param student_id: 学生ID
    @return: JsonResponse 删除结果
    """
    try:
        class_id = None
        try:
            with connection.cursor() as cursor:
                # 获取学生信息（主要是班级ID用于更新班级学生数）
                cursor.execute("SELECT class_id FROM yzh_students WHERE id = %s", [student_id])
                result = cursor.fetchone()
                if not result:
                    return JsonResponse({
                        'status': 'error',
                        'message': f'学生 (ID: {student_id}) 不存在'
                    }, status=404)
                
                class_id = result[0]
                
                # 删除学生记录
                cursor.execute("DELETE FROM yzh_students WHERE id = %s", [student_id])
                
                # 更新班级学生数量
                if class_id:
                    cursor.execute("UPDATE yzh_classes SET student_count = student_count - 1 WHERE id = %s", 
                                  [class_id])
        except Exception as e:
            logger.exception(f"数据库操作失败: {str(e)}")
            return JsonResponse({
                'status': 'error',
                'message': f'数据库操作失败: {str(e)}'
            }, status=500)
        
        # 返回极简响应
        return JsonResponse({
            'status': 'success',
            'message': '删除学生成功'
        }, safe=True, json_dumps_params={'ensure_ascii': False})
    except Exception as e:
        logger.exception(f"删除学生失败: {str(e)}")
        return JsonResponse({'status': 'error', 'message': '服务器内部错误'}, status=500)

@require_http_methods(["GET"])
def yzh_student_detail(request, student_id):
    """
    获取学生详情
    @description 获取指定学生的详细信息
    @param request: HTTP请求
    @param student_id: 学生ID
    @return: JsonResponse 学生详情
    """
    try:
        with connection.cursor() as cursor:
            # 查询学生基本信息，包括关联的班级、系部、学院
            cursor.execute("""
                SELECT s.id, s.student_code, s.name, s.gender, s.enrollment_date,
                       s.class_id, c.name as class_name,
                       s.department_id, d.name as department_name,
                       s.college_id, co.name as college_name,
                       s.status, s.create_time
                FROM yzh_students s
                LEFT JOIN yzh_classes c ON s.class_id = c.id
                LEFT JOIN yzh_departments d ON s.department_id = d.id
                LEFT JOIN yzh_colleges co ON s.college_id = co.id
                WHERE s.id = %s
            """, [student_id])
            result = dictfetchall(cursor)
            
            if not result:
                return JsonResponse({
                    'status': 'error',
                    'message': '学生不存在'
                }, status=404)
                
            student = result[0]
            
        # 返回学生详情
        return JsonResponse({
            'status': 'success',
            'message': '获取学生详情成功',
            'data': {
                'student': student
            }
        })
    except Exception as e:
        logger.exception(f"获取学生详情失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'message': f'获取学生详情失败: {str(e)}'
        }, status=500)

@require_http_methods(["GET"])
def check_yzh_tables_structure(request):
    """
    检查yzh_students和yzh_classes表的结构
    """
    try:
        table_info = {}
        
        with connection.cursor() as cursor:
            # 检查yzh_classes表结构
            try:
                cursor.execute("SHOW COLUMNS FROM yzh_classes")
                table_info['yzh_classes'] = dictfetchall(cursor)
                logger.info(f"成功获取yzh_classes表结构")
            except Exception as e:
                logger.error(f"获取yzh_classes表结构失败: {str(e)}")
                table_info['yzh_classes_error'] = str(e)
            
            # 检查yzh_students表结构
            try:
                cursor.execute("SHOW COLUMNS FROM yzh_students")
                table_info['yzh_students'] = dictfetchall(cursor)
                logger.info(f"成功获取yzh_students表结构")
            except Exception as e:
                logger.error(f"获取yzh_students表结构失败: {str(e)}")
                table_info['yzh_students_error'] = str(e)
            
            # 检查数据库中是否有学生数据
            try:
                cursor.execute("SELECT COUNT(*) FROM yzh_students")
                result = cursor.fetchone()
                table_info['yzh_students_count'] = result[0] if result else 0
                
                # 检查是否有ID=4的班级的学生
                cursor.execute("SELECT COUNT(*) FROM yzh_students WHERE class_id = %s", [4])
                result = cursor.fetchone()
                table_info['class_4_students_count'] = result[0] if result else 0
            except Exception as e:
                logger.error(f"检查学生数据失败: {str(e)}")
                table_info['students_data_error'] = str(e)
        
        return JsonResponse({
            'status': 'success',
            'message': '数据库表结构检查完成',
            'data': table_info
        })
        
    except Exception as e:
        logger.exception(f"检查数据库表结构失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'message': f'检查数据库表结构失败: {str(e)}'
        }, status=500) 
